The data dictionary is full of “Metadata”, information about what is going-on inside your database. The data dictionary is presented to us in the form of a number of views. The dictionary views come in two primary forms:

The DBA, ALL or USER views

- These views are used to manage database structures.

The V$ Dynamic Performance Views

- These views are used to monitor real time database statistics

Throughout the rest of this book we will introduce you to data dictionary views that you can use to manage your database. You will find the entire list of Oracle Data Dictionary views documented in the Oracle documentation online.

There are hundreds of views in the data dictionary.  To see the depth of the data dictionary views, here are the views that store data about Oracle tables:

* dba_all_tables

* dba_indexes

* dba_ind_partitions

* dba_ind_subpartitions

* dba_object_tables

* dba_part_col_statistics

* dba_subpart_col_statistics

* dba_tables

* dba_tab_cols

* dba_tab_columns

* dba_tab_col_statistics

* dba_tab_partitions

* dba_tab_subpartitions

Later in this chapter we will see simple data dictionary scripts to see information about the internal structure of our datafiles, tablespaces, tables and indexes.  To learn more about the data dictionary, get the free Oracle 10g data dictionary reference by Rampant (Just Google “bc free 10g poster” to order your copy).  For a collection of pre-written Oracle data dictionary scripts, see http://www.oracle-script.com/ .

Tip:  You can also query the DICT or DICTIONARY view to see a list of all views and comments about them that exist in the data dictionary.  This view is a quick way to find exactly what you’re looking for in the data dictionary.

Inside the Oracle Data Dictionary

If you are like me, you are a bit forgetful. The data dictionary is a repository of information about the Oracle database, known as metadata. Metadata is “information about information,” and the data dictionary is information about the database. In this section we want to show you how to use the data dictionary to get information on tables.

Oracle provides several data dictionary views that you can use to collect information on views in the database. These views include:

* dba_tables, all_tables, user_tables

* dba_tab_columns, all_tab_columns and user_tab_columns

So, we forgot where the BOOKS table is located. From the SYSTEM account, we can query the dba_tables view to find our table:

Other views that show you where your tables are include user_tables and all_tables.

Oracle also provides views that allow you to view the attributes of table columns. The dba_tab_columns view (and all_tab_columns and user_tab_columns) gives you a variety of information on table columns.

Oracle Data Dictionary

Managing Oracle requires the use of a number of Oracle supplied views. These views include the data dictionary and the dynamic performance views. Together these views allow you to:

* Manage the database

* Tune the database

* Monitor the database

In this chapter we will fist look at the data dictionary views. We will then look at the dynamic performance views available in Oracle Database 10g.

At the heart of every Oracle database is the data dictionary. The data dictionary is generated when the database is first created. In this section we will discuss the Oracle data dictionary. In it we will discuss:

* The purpose of the data dictionary

* The architecture of the data dictionary

* Uses of the data dictionary

The Purpose of the Data Dictionary

Metadata is data about data, or data that defines other data. The Oracle data dictionary is metadata about the database. For example, if you create a table in Oracle, metadata about that table is stored in the data dictionary. Such things as column names, length, and other attributes are stored. Thus, the data dictionary contains a great volume of useful information about your database. Pretty much everything you would want to know about your database is contained in the data dictionary in some form.

As a DBA then, you can see why the data dictionary is so important. Since you can’t possibly remember everything about your database (like the names of all the tables and columns) Oracle remembers this for you. All you need to do is learn how to find that information. We will be showing you how to do this in a later section in this book.

The Architecture of the Data Dictionary

The data dictionary is created when the Oracle database is created. It is owned by the SYS user, and is stored principally in the SYSTEM tablespace, though some components are stored in the SYSAUX tablespace in Oracle Database 10g.

The data dictionary is comprised of a number of tables and Oracle views. Oracle wants you to keep your hands off these tables, and unless you are a real expert I’d recommend you do just that.

Of course, the data dictionary would be pretty worthless if we could not access the data. Oracle supplies a number of views that you can query that will give you direct access into the data dictionary tables. These views are generally tuned by Oracle for quick access to the underlying objects and the names of the views often reflect the use of that view much better than the names of the underlying objects.  The data dictionary views come in three main flavors:

* User views

* All views

* DBA views

For example, if you want to look at user information there are three views, USER_USERS, ALL_USERS and dba_users. Each of these views sees the user a bit differently.

All views that start with USER only sees the information that pertains to the user you are logged in as. For example, if you are logged in as SCOTT, when you look at the user_tables view, you will only see information on tables that are owned by the SCOTT user. You might have access to tables in the GEORGE schema, but you won’t see them in the user_tables view. Here is an example of a simple query against the user_tables view:

SELECT table_name FROM user_tables;

The ALL views allow you to see all objects that you have access to. For example, if you are logged in as SCOTT and you query the all_tables view, you will see all the tables owned by SCOTT but you will also see any tables you have access to that are owned by GEORGE, or any other user. You have to have access rights to these objects (which you would have received via the grant command which we discussed in an earlier chapter).

Generally the two main differences between the USER and ALL views is that the owner of the object is included in the ALL views, and this is not included in the USER views which makes sense since you will only be seeing your objects. In this example, we query the all_tables view for all tables that start with EMP:

The granddaddy of the data dictionary views are the DBA views. These views are unrestricted windows into all Oracle data dictionary objects. Because of this, they are only accessible by DBA’s (as the name seems to suggest). All DBA views start with DBA. In this example, we query the dba_tables view for all tables that start with EMP and owned by users whose names start with ROBERT:

You can find the data dictionary tables documented in the Oracle Database 10g Reference Guide, which is part of the overall Oracle database documentation set. There are almost 600 DBA views in Oracle Database 10g alone, and a like number of USER and ALL views. The ALL and USER views are pretty much children of the DBA views, and you will not find ALL or USER views for each DBA view.  You can also find the views documented within the data dictionary itself.  The DICTIONARY (or DICT for short) view contains all the tables of the data dictionary, plus comments on what each table is used for.

Data Dictionary Scripts

Like many things, using the data dictionary takes some practice. It takes understanding what you want to find, and then looking at the view, figuring out how to find the view. Sometimes, of course, you will find yourself having to join two, three or more views together to get the answer you need.

To give you a start, in this section we are going to provide you with some example queries against the data dictionary. These will be queries that you might use in your early DBA exploits. Most DBA’s have a collection of data dictionary scripts right at hand (I confess, we get a big thrill out of just typing queries as we sit at the computer, testing my data dictionary knowledge).

In this section we will provide you with examples of how to:

* Determine what users are setup in your database

* Determine what tablespaces are configured in your database, and where the related datafiles are located.

* Determine who owns a specific table and its tablespace.

* Determine what indexes are associated with a specific table.

Hopefully these examples will give you some insight into how you can use the data dictionary to manage your database.

These examples are designed to give you some ideas of how you can use the data dictionary views to manage your database. Very often, good DBA’s will put scripts together that run on a regular schedule via CRON or some other scheduling facility. These scripts will monitor the database looking for problems, like running out of disk space.

There are tons of scripts out on the internet that you can use for purposes like this. We want you to understand what these scripts are doing though, and that is what these examples are for. Use them to learn about how the data dictionary works, and to see the powerful information it provides.

This is an excerpt from the bestselling "Easy Oracle Jumpstart" by Robert Freeman and Steve Karam (Oracle ACE and Oracle Certified Master).  It’s only $19.95 when you buy it directly from the publisher here.

If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.